/**
 * Name: net.uni.ap.dao.AbstractBaseDAO.java
 * Version: 1.0
 * Date: 2011-11-24
 * Author: 孙伟
 */
package net.uni.ap.dao;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Map;

import net.uni.ap.exp.DAOException;
import net.uni.util.HibernateConfigurationHelper;
import net.uni.util.NumberUtil;
import net.uni.util.Page;
import net.uni.util.ReflectionUtils;
import net.uni.util.StringUtils;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.Projections;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowMapper;


/**
 * 处理内容:顶层DAO抽象类
 * 所有dao需要继承此抽象类
 * @param <T> 泛型
 * @version: 1.0
 * @see:net.uni.ap.dao.AbstractBaseDAO.java
 * @date:2011-11-24
 * @author:孙伟
 */
public abstract class AbstractBaseDAO<T> extends BaseDaoSupport implements IBaseDAO<T>{

	private static final String UNCHECKED = "unchecked";
	protected Class<T> modelClass;
	protected transient  Log log = LogFactory.getLog(getClass());
	
	public AbstractBaseDAO(){
		this.modelClass=ReflectionUtils.getSuperClassGenricType(getClass());
	}
	public AbstractBaseDAO( Class<T> modelClass) {
	    this.modelClass = modelClass;
	}
	public void myTest(Object o){
		this.flush();
		List list =	this.getSession().createSQLQuery( "select * from cs_order t where T.ORDER_ID = ?").setLong(0, NumberUtil.createLong(o)).list();
		for(Object obj:list /*l为查询出来的集合*/ ){
			 Object[] oo = (Object[])obj;
			 for(Object bj:oo){
			  System.out.print(""+bj +"---");
			 }
			 System.out.println("  换行");
			}
	System.out.println(o);
	}
	/**
	 * @param object
	 * @throws DAOException
	 * @方法说明 保存对象
	 * @date 2011-11-24
	 * @author 孙伟
	 */
	public void create( T object) throws DAOException{
		try{
			this.getHibernateTemplate().save(object);
		}catch(Exception e){
			this.logger.error("保存对象失败", e);
			throw new DAOException("保存对象失败",e);
		}
	}
	public void create(Class<?> clazz, Object object)throws DAOException{
		try {
			this.getHibernateTemplate().save(clazz.getName(), object);
		} catch (DataAccessException e) {
			throw new DAOException("保存对象失败",e);
		}
	}
	
	/**
	 * @param object
	 * @throws DAOException
	 * @方法说明 传入entityname
	 * @date 2012-4-18
	 * @author 孙伟
	 */
	public void createByEntity(T object) throws DAOException{
		try{
			this.getHibernateTemplate().save(this.modelClass.getName(),object);
		}catch(Exception e){
			this.logger.error("保存对象失败", e);
			throw new DAOException("保存对象失败",e);
		}
	}
	
	/**
	 * @param object
	 * @throws DAOException
	 * @方法说明 传入entityname
	 * @date 2012-4-18
	 * @author 孙伟
	 */
	public void updateByEntity(T object) throws DAOException{
		try{
			this.getHibernateTemplate().update(this.modelClass.getName(),object);
		}catch(Exception e){
			this.logger.error("保存对象失败", e);
			throw new DAOException("保存对象失败",e);
		}
	}

	/**
	 * @param object
	 * @throws DAOException
	 * @方法说明 修改对象
	 * @date 2011-11-24
	 * @author 孙伟
	 */
	public void update( T object) throws DAOException{
		try{
			this.getHibernateTemplate().update(object);
		}catch(Exception e){
			this.logger.error("修改对象失败", e);
			throw new DAOException("修改对象失败",e);
		}
	}
	
	public void update(Class<?> clazz, Object object) throws DAOException{
		try {
			this.getHibernateTemplate().update(clazz.getName(), object);
		} catch (Exception e) {
			throw new DAOException("更新对象失败");
		}
	}

	/**
	 * 保存或更新对象
	 * @param object
	 * @throws DAOException
	 */
	public void saveOrUpdate( T object) throws DAOException{
		try{
			this.getHibernateTemplate().saveOrUpdate(object);
		}catch(Exception e){
			this.logger.error("修改对象失败", e);
			throw new DAOException("修改对象失败",e);
		}
	}
	/**
	 * @param object
	 * @throws DAOException
	 * @方法说明 修改或更新对象集合
	 */
	public void saveOrUpdateAll( Collection<T> entities)  throws DAOException{
		try{
			this.getHibernateTemplate().saveOrUpdateAll(entities);
		}catch(Exception e){
			this.logger.error("修改对象集合失败", e);
			throw new DAOException("修改对象集合失败",e);
		}
	}

	/**
	 * @param id 修改或者删除那条记录的主键ID
	 * @return 插入条数
	 * @throws DAOException
	 * @方法说明 删除或者修改主表数据的时候，调用此方法往B表插入一条记录
	 * @date"unchecked" 2012-1-13
	 * @author 孙伟
	 */
	@SuppressWarnings(UNCHECKED)
	public int saveHisData(Long id) throws DAOException{
		try{
			String tableName = HibernateConfigurationHelper.getTableName(modelClass);
			StringBuffer colStr = new StringBuffer();
			colStr.append("SELECT S.COLUMN_NAME FROM USER_TAB_COLUMNS S WHERE S.TABLE_NAME = ? ORDER BY S.COLUMN_ID");
			List<String> listCol = this.getSession().createSQLQuery(colStr.toString()).setString(0, tableName).list();
			//如果从USER_TAB_COLUMNS中未查到数据，则从DBA_TAB_COLUMNS中查询
			if(null == listCol || listCol.isEmpty()){
				colStr = new StringBuffer();
				colStr.append("SELECT S.COLUMN_NAME");
				colStr.append("  FROM DBA_TAB_COLUMNS S");
				colStr.append(" WHERE S.TABLE_NAME = ?");
				colStr.append("   AND S.OWNER = (SELECT T.OWNER");
				colStr.append("                    FROM DBA_TAB_COLUMNS T");
				colStr.append("                   WHERE T.TABLE_NAME = ?");
				colStr.append("                     AND ROWNUM = 1)");
				colStr.append(" ORDER BY S.COLUMN_ID");
				listCol = this.getSession().createSQLQuery(colStr.toString()).setString(0, tableName).setString(1, tableName).list();
			}
			if(null != listCol && listCol.size() > 0){
				StringBuffer sbf = new StringBuffer();
				sbf.append("INSERT INTO ");
				sbf.append(tableName);
				sbf.append("_B(");
				StringBuffer col = new StringBuffer();
				for(int i=0;i<listCol.size();i++){
					if(i==0){
						col.append(listCol.get(i).toString());
					}else{
						col.append(",");
						col.append(listCol.get(i).toString());
					}
				}
				sbf.append(col);
				sbf.append(") ");
				sbf.append(" SELECT ");
				sbf.append(col);
				sbf.append(" FROM ");
				sbf.append(tableName);
				sbf.append(" R WHERE ");
				sbf.append(HibernateConfigurationHelper.getPkColumnName(modelClass));
				sbf.append("=?");
				return this.getSession().createSQLQuery(sbf.toString()).setLong(0,id).executeUpdate();
			}else{
				return 0;
			}
			//return this.getJdbcTemplate().update(sbf.toString(), id);
		}catch(Exception e){
			this.logger.error("删除或者修改主表数据的时候，调用此方法往B表插入一条记录失败", e);
			throw new DAOException("删除或者修改主表数据的时候，调用此方法往B表插入一条记录失败",e);
		}
	}
	
	/**
	 * @param entityList 修改或者删除那条记录的主键ID的list
	 * @return 插入条数
	 * @throws DAOException
	 * @方法说明 删除或者修改主表数据的时候，调用此方法往B表插入多条数据
	 * @date 2012-1-13
	 * @author 孙伟
	 */
	public int saveHisData(List<Long> entityList) throws DAOException{
		try{
			String tableName = HibernateConfigurationHelper.getTableName(modelClass);
			StringBuffer colStr = new StringBuffer();
			colStr.append("SELECT S.COLUMN_NAME FROM USER_TAB_COLUMNS S WHERE S.TABLE_NAME = ? ORDER BY S.COLUMN_ID");
			List<String> listCol = this.getSession().createSQLQuery(colStr.toString()).setString(0, tableName).list();
			StringBuffer sbf = new StringBuffer();
			sbf.append("INSERT INTO ");
			sbf.append(tableName);
			sbf.append("_B(");
			StringBuffer col = new StringBuffer();
			for(int i=0;i<listCol.size();i++){
				if(i==0){
					col.append(listCol.get(i).toString());
				}else{
					col.append(",");
					col.append(listCol.get(i).toString());
				}
			}
			sbf.append(col);
			sbf.append(") ");
			sbf.append(" SELECT ");
			sbf.append(col);
			sbf.append(" FROM ");
			sbf.append(tableName);
			sbf.append(" R WHERE ");
			sbf.append(HibernateConfigurationHelper.getPkColumnName(modelClass));
			sbf.append(" in(");
			for(int i=0;i<entityList.size();i++){
				sbf.append(entityList.get(i));
				if(i<(entityList.size()-1)){
					sbf.append(",");
				}
			}
			sbf.append(")");
			return this.getSession().createSQLQuery(sbf.toString()).executeUpdate();
			//return this.getJdbcTemplate().update(sbf.toString(), id);
		}catch(Exception e){
			this.logger.error("删除或者修改主表数据的时候，调用此方法往B表插入一条记录失败", e);
			throw new DAOException("删除或者修改主表数据的时候，调用此方法往B表插入一条记录失败",e);
		}
	}

	/**
	 * @param entityList 修改或者删除那条记录的主键ID的数组
	 * @return 插入条数
	 * @throws DAOException
	 * @方法说明 删除或者修改主表数据的时候，调用此方法往B表插入多条数据
	 * @date 2012-1-13
	 * @author 孙伟
	 */
	public int saveHisData(Long[] entityList) throws DAOException{
		try{
			String tableName = HibernateConfigurationHelper.getTableName(modelClass);
			StringBuffer colStr = new StringBuffer();
			colStr.append("SELECT S.COLUMN_NAME FROM USER_TAB_COLUMNS S WHERE S.TABLE_NAME = ? ORDER BY S.COLUMN_ID");
			List<String> listCol = this.getSession().createSQLQuery(colStr.toString()).setString(0, tableName).list();
			StringBuffer sbf = new StringBuffer();
			sbf.append("INSERT INTO ");
			sbf.append(tableName);
			sbf.append("_B(");
			StringBuffer col = new StringBuffer();
			for(int i=0;i<listCol.size();i++){
				if(i==0){
					col.append(listCol.get(i).toString());
				}else{
					col.append(",");
					col.append(listCol.get(i).toString());
				}
			}
			sbf.append(col);
			sbf.append(") ");
			sbf.append(" SELECT ");
			sbf.append(col);
			sbf.append(" FROM ");
			sbf.append(tableName);
			sbf.append(" R WHERE ");
			sbf.append(HibernateConfigurationHelper.getPkColumnName(modelClass));
			sbf.append(" in(");
			for(int i=0;i<entityList.length;i++){
				sbf.append(entityList[i]);
				if(i<(entityList.length-1)){
					sbf.append(",");
				}
			}
			sbf.append(")");
			return this.getSession().createSQLQuery(sbf.toString()).executeUpdate();
			//return this.getJdbcTemplate().update(sbf.toString(), id);
		}catch(Exception e){
			this.logger.error("删除或者修改主表数据的时候，调用此方法往B表插入一条记录失败", e);
			throw new DAOException("删除或者修改主表数据的时候，调用此方法往B表插入一条记录失败",e);
		}
	}
	
	/**
	 * @param page 分页对象
	 * @param orderByProName 需要排序的字段java属性名称，默认是降序
	 * @param id 需要查询数据的主键id值，可以为null
	 * @return 返回B表的分页数据
	 * @throws DAOException
	 * @方法说明 B表数据统一查询方法
	 * @date 2012-1-13
	 * @author 孙伟
	 */
	public Page selectHisData(Page page,String orderByProName,Long id) throws DAOException{
		try{
			StringBuffer sbCount = new StringBuffer();
			sbCount.append("select count(1) as count from ");
			sbCount.append(HibernateConfigurationHelper.getTableName(modelClass));
			int count = Integer.parseInt(this.getSession().createSQLQuery(sbCount.toString()).uniqueResult().toString());
			if(count>0){
				page.setTotalrows(count);
				page.excecute();
				StringBuffer sbf = new StringBuffer();
				sbf.append("select * from ");
				sbf.append(HibernateConfigurationHelper.getTableName(modelClass));
				if(id!=null){
					sbf.append(" where ");
					sbf.append(HibernateConfigurationHelper.getPkColumnName(modelClass));
					sbf.append("=?");
				}
				sbf.append(" order by ");
				if(orderByProName!=null&&!orderByProName.trim().equals("")){
					sbf.append(HibernateConfigurationHelper.getColumnName(modelClass, orderByProName));
					sbf.append(" desc nulls last");
					sbf.append(",");
				}
				sbf.append(HibernateConfigurationHelper.getPkColumnName(modelClass));
				
				Query query = this.getSession().createSQLQuery(sbf.toString()).addEntity(modelClass);
				
				if(id!=null){
					query.setLong(0,id);
				}
				query.setFirstResult(page.getStart()).setMaxResults(page.getRowsize());
				page.setList(query.list());
			}
		}catch(Exception e){
			this.logger.error("删除或者修改主表数据的时候，调用此方法往B表插入一条记录失败", e);
			throw new DAOException("删除或者修改主表数据的时候，调用此方法往B表插入一条记录失败",e);
		}
		return page;
	}
	/**
	 * @param id 要删除对象的ID
	 * @throws DAOException
	 * @方法说明 删除对象
	 * @date 2011-11-24
	 * @author 孙伟
	 */
	public void delete( Serializable id) throws DAOException{
		try{
			Object obj = this.getHibernateTemplate().get(modelClass, id);
			if(obj!=null)
				this.getHibernateTemplate().delete(obj);
		}catch(Exception e){
			this.logger.error("删除对象失败", e);
			throw new DAOException("删除对象失败",e);
		}
	}
	
	/**
	 * @param 
	 * @throws DAOException
	 * @方法说明 删除对象
	 * @date 2011-11-24
	 * @author 孙伟
	 */
	public void delete(List<Long> entityList) throws DAOException{
		try{
			for(Long entity:entityList)
			    this.delete(entity);
		}catch(Exception e){
			this.logger.error("删除对象失败", e);
			throw new DAOException("删除对象失败",e);
		}
	}
	
	public void deleteByEntity(Class<?> clazz,Serializable id)throws DAOException{
		try{
			this.getHibernateTemplate().delete(this.getHibernateTemplate().get(clazz.getName(), id));
		}catch(Exception e){
			this.logger.error("根据实体类型删除对象失败", e);
			throw new DAOException("根据实体类型删除对象失败",e);
		}
	}
	
	
	/**
	 * @param object 要删除对象
	 * @throws DAOException
	 * @方法说明 删除对象
	 */
	public void delete( T object) throws DAOException{
		try{
			this.getHibernateTemplate().delete(object);
		}catch(Exception e){
			this.logger.error("删除对象失败", e);
			throw new DAOException("删除对象失败",e);
		}
	}

	/**
	 * @param ids 需要删除的对象id数组
	 * @throws DAOException
	 * @方法说明 批量删除对象
	 * @date 2011-11-24
	 * @author Administrator
	 */
	public void delete( Serializable[] ids) throws DAOException{
		try{
			for(Serializable id : ids){
				this.delete(Long.valueOf(id.toString()));
			}
		}catch(Exception e){
			this.logger.error("删除对象失败", e);
			throw new DAOException("删除对象失败",e);
		}
	}
	

	public void deleteByEntity(Class<?> clazz,Serializable ids[])throws DAOException{
		try{
			for(Serializable id : ids)
				this.deleteByEntity(clazz,Long.valueOf(id.toString()));
		}catch(Exception e){
			this.logger.error("删除对象失败", e);
			throw new DAOException("删除对象失败",e);
		}
	}

	/**
	 * 对象不存在时返回空
	 * @param id
	 * @return
	 * @throws DAOException
	 * @方法说明 通过id获取对象
	 * @date 2011-11-24
	 * @author 孙伟
	 */
	public T getObject( Serializable id) throws DAOException{
		return getObject(this.modelClass, id);
	}

	/**
	 * @param <X>
	 * @param clazz
	 * @param id
	 * @return
	 * @throws DAOException
	 * @方法说明 根据id和class查询对象实体
	 * @date 2011-11-29
	 * @author HeDroid
	 */
	public <X> X getObject( Class<X> clazz,  Serializable id) throws DAOException{
		try{
			return (X) getHibernateTemplate().get(clazz, id);
		}catch(Exception e){
			this.logger.error("通过id获取对象失败", e);
			throw new DAOException("通过id获取对象失败",e);
		}
	}
	/**
	 * @param id
	 * @return
	 * @throws DAOException
	 * @方法说明 通过id获取对象 对象不存在时抛出异常
	 * @date 2011-11-24
	 * @author 孙伟
	 */
	public T loadObject( Serializable id) throws DAOException{
		try{
			return (T) getHibernateTemplate().load(this.modelClass, id);
		}catch(Exception e){
			this.logger.error("通过id获取对象失败", e);
			throw new DAOException("通过id获取对象失败",e);
		}
	}

	/**
	 * 从数据库读取所有对象
	 *
	 * @return List
	 * @throws DAOException
	 */
	public List<T> getList() throws DAOException {
		try {
			return (this.getHibernateTemplate().loadAll(this.modelClass));
		} catch (Exception e) {
			log.error("Error occurred when getList methods of AbstractBaseDAO.",e);
			throw new DAOException("查询失败", e);
		}
	}
	/**
	 * @param params 查询的属性名
	 * @param values 属性名
	 * @return
	 * @throws DAOException
	 * @方法说明
	 * @date 2011-12-1
	 * @author HeDroid
	 */
	public List<T> getList(String[] params,Object[] values) throws DAOException {
		try {
			if(params==null || params.length==0){
				return this.getList();
			}else{
				StringBuffer sbf=new StringBuffer("from " + modelClass.getName()+" where 1=1 ");
				for(int i = 0 ; i < params.length ; i++){
					sbf.append(" and ").append(params[i]).append(" =? ");
				}
				return this.getHibernateTemplate().find(sbf.toString(), values);
			}

		} catch (Exception e) {
			log.error("Error occurred when getList methods of AbstractBaseDAO.",e);
			throw new DAOException("查询失败", e);
		}
	}

	/**
	 * @param sql 查询sql
	 * @param obj 按照sql中绑定变量的顺序的实际value
	 * @return 查询的Object的集合
	 * @throws DAOException
	 * @方法说明
	 * @date 2011-11-29
	 * @author HeDroid
	 */
	protected List  getListBySql( String sql, Object...objs) throws DAOException {
		try {
			SQLQuery query= this.getSession().createSQLQuery(sql);
			if(objs!=null)
				for(int i = 0;i < objs.length; i++){
					query.setParameter(i, objs[i]);
				}
			return query.list();
		} catch (Exception e) {
			e.printStackTrace();
			log.error("Error occurred when getList methods of AbstractBaseDAO.",e);
			throw new DAOException("查询失败", e);
		}
	}

	/**
	 * @param sql 查询sql
	 * @param param 按照绑定变量的map，为<变量名，变量值>的map
	 * @return	查询的Object的集合
	 * @throws DAOException
	 * @方法说明
	 * @date 2011-11-29
	 * @author HeDroid
	 */
	protected List  getListBySql( String sql, Map<String,Object> param) throws DAOException {
		try {
			SQLQuery query=this.getSession().createSQLQuery(sql);
			if(param!=null){
				for(String key : param.keySet()){
					Object value=param.get(key);
					if (value instanceof Collection) {
						query.setParameterList(key, (Collection) value);
					}
					else if (value instanceof Object[]) {
						query.setParameterList(key, (Object[]) value);
					}
					else {
						query.setParameter(key, value);
					}
				}
			}
			return query.list();
		} catch (Exception e) {
			log.error("Error occurred when getList methods of AbstractBaseDAO.",e);
			throw new DAOException("查询失败", e);
		}
	}
	
	/**
	 * @param sql 查询sql
	 * @param param 按照绑定变量的map，为<变量名，变量值>的map
	 * @return	查询的Object的集合
	 * @throws DAOException
	 * @方法说明
	 * @date 2011-11-29
	 * @author HeDroid
	 */
	public List  getListMapBySql( String sql, Map<String,Object> param) throws DAOException {
		try {
			SQLQuery query=this.getSession().createSQLQuery(sql);
			if(param!=null){
				for(String key : param.keySet()){
					Object value=param.get(key);
					if (value instanceof Collection) {
						query.setParameterList(key, (Collection) value);
					}
					else if (value instanceof Object[]) {
						query.setParameterList(key, (Object[]) value);
					}
					else {
						query.setParameter(key, value);
					}
				}
			}
			return query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).list();
		} catch (Exception e) {
			log.error("Error occurred when getList methods of AbstractBaseDAO.",e);
			throw new DAOException("查询失败", e);
		}
	}
	/***
	 * @param instance 返回的对象
	 * @param page 分页
	 * @return
	 * @throws DAOException
	 * @方法说明 单表分页查询
	 * @date 2011-12-5
	 * @author HeDroid
	 */
	public Page findByEntity(T instance,Page page) throws DAOException{
		Criteria cri=this.getSession().createCriteria(modelClass);
		Example example=Example.create(instance);
		//用自定义的选择器，去除空值还有null值
		example.excludeZeroes();
//		example = example.setPropertySelector(NOT_NULL_NOT_EMPTY);
		cri.add(example);
		cri.setProjection(Projections.rowCount());
		int count=(Integer) Integer.parseInt(cri.uniqueResult().toString());
		page.setTotalrows(count);
		page.excecute();
		if(count > 0){
				Criteria criteria=this.getSession().createCriteria(modelClass);
				criteria.add(example);
				//rowsize>0时进行分页查询，否则查询全部
				if(page.getRowsize()>0)
					criteria.setFirstResult(page.getStart()).setMaxResults(page.getRowsize());
				page.setList(criteria.list());
		}
		//List	list=this.getHibernateTemplate().findByExample(instance, page.getStart(), page.getRowsize());
		//page.setList(list);
		return page;
	}

	/**
	 * @param hql queryString
	 * @param page
	 * @param objects 参数值数组
	 * @return 分页对象
	 * @throws DAOException
	 * @方法说明 以“?”形式的绑定变量的参数， 默认hql查询
	 * @date 2011-11-30
	 * @author HeDroid
	 */
	protected Page getPageByHql( String hql,  Page page , Object...objects) throws DAOException{
		return this.getList(hql, true, false,null,null, page, null, objects);
	}
	/**
	 * @param hql queryString
	 * @param page
	 * @param objects 参数值数组
	 * @return 分页对象
	 * @throws DAOException
	 * @方法说明 以“?”形式的绑定变量的参数， 默认hql查询
	 * @date 2011-11-30
	 * @author HeDroid
	 */
	protected Page getPageByHql( String hql,  Page page ,  Map<String,Object> param) throws DAOException{
		return this.getList(hql, true, true,null,null, page, param);
	}
	/**
	 * @param sql queryString
	 * @param page
	 * @param objects 参数值数组
	 * @return 分页对象
	 * @throws DAOException
	 * @方法说明 以“?”形式的绑定变量的参数， sql查询
	 * @date 2011-11-30
	 * @author HeDroid
	 */
	protected Page getPageBySql( String sql,  Page page , Object...objects) throws DAOException{
		return this.getList(sql, false, false, null,null,page, null, objects);
	}

	/**
	 * @param sql
	 * @param alices sql中的别名数组
	 * @param clazzs sql中的别名对应的对象的Class
	 * @param page
	 * @param objects
	 * @return 分页对象
	 * @throws DAOException
	 * @方法说明
	 * @date 2011-12-7
	 * @author HeDroid
	 */
	protected Page getPageBySql(String sql, String[] alices, Class[] clazzs,Page page, Object... objects) throws DAOException {
		return this.getList(sql, false, false,alices,clazzs, page, null, objects);
	}
	/**
	 * @param sql queryString
	 * @param page
	 * @param param
	 * @return 分页对象
	 * @throws DAOException
	 * @方法说明   以别名形式的绑定变量的参数， 默认hql查询
	 * @date 2011-11-30
	 * @author HeDroid
	 */
	protected Page getPageBySql( String sql, Page page ,  Map<String,Object> param) throws DAOException{
		return this.getList(sql, false, true,null,null, page, param);
	}
	/**
	 * @param sql
	 * @param alices sql中的别名数组
	 * @param clazzs sql中的别名对应的对象的Class
	 * @param page
	 * @param param
	 * @return 分页对象
	 * @throws DAOException
	 * @方法说明
	 * @date 2011-12-7
	 * @author HeDroid
	 */
	protected Page getPageBySql( String sql, String[] alices, Class[] clazzs, Page page ,  Map<String,Object> param) throws DAOException{
		return this.getList(sql, false, true,alices,clazzs,page,param,null);
	}

	/**
	 * @param queryString
	 * @param isHql 是否是hql查询，true创建Query，否则创建SQLQuery
	 * @param isAlice 绑定变量的形式，true为别名绑定，false为“？”形式绑定。
	 * @param page
	 * @param param
	 * @param objects
	 * @return
	 * @throws DAOException
	 * @方法说明
	 * @date 2011-11-30
	 * @author HeDroid
	 */
	protected Page getList( String queryString,boolean isHql,boolean isAlice, String[] alices, Class[] clazzs, Page page , Map<String,Object> param,Object...objects) throws DAOException{
		try{
			if(page.getTotalrows()==0)
				page.setTotalrows(getCount(queryString,isHql,isAlice,param,objects));
			page.excecute();
			//数据总数大于0的时候进行查询
			if(page.getTotalrows()>0){
				Query query=null;
				if(isHql){
					query=this.getSession().createQuery(queryString);
				}else{//创建SQLQuery
					query=this.getSession().createSQLQuery(queryString);
					//给别名赋值
					if(alices!=null && alices.length>0 && clazzs!=null && clazzs.length>0 && alices.length==clazzs.length)
						for(int i=0;i<alices.length;i++)
							((SQLQuery)query).addEntity(alices[i], clazzs[i]);
				}
				if(isAlice){//map形式的变量
					if(param!=null){
						for(String key:param.keySet()){
							Object value=param.get(key);
							if (value instanceof Collection) {
								query.setParameterList(key, (Collection) value);
							}
							else if (value instanceof Object[]) {
								query.setParameterList(key, (Object[]) value);
							}
							else {
								query.setParameter(key, value);
							}
						}
					}
				}else{//不定参形式的数据形式的绑定变量
					for(int i = 0;i < objects.length; i++){
						query.setParameter(i, objects[i]);
					}
				}
				//rowsize>0时进行分页查询，否则查询全部
				if(page.getRowsize()>0)
					query.setFirstResult(page.getStart()).setMaxResults(page.getRowsize());
				page.setList(query.list());
			}
			return page;
		}catch(Exception e){
			log.error("Error occurred when findByEntity methods of AbstractBaseDAO.",e);
			throw new DAOException("分页查询失败", e);
		}
	}
	/**
	 * @param queryString
	 * @param objects	queryString中绑定的变量
	 * @return	查询总量
	 * @throws DAOException
	 * @方法说明 以“?”形式的绑定变量的hql查询
	 * @date 2011-11-30
	 * @author HeDroid
	 */
	protected int getCount( String queryString, Object...objects) throws DAOException{
		return this.getCount(queryString, true, false,null, objects);
	}
	/**
	 * @param queryString
	 * @param param
	 * @return 查询总量
	 * @throws DAOException
	 * @方法说明 默认hql以别名绑定变量的形式查询
	 * @date 2011-11-30
	 * @author HeDroid
	 */
	protected int getCount( String queryString, Map<String,Object> param) throws DAOException{
		return this.getCount(queryString, true, true,param);
	}
	/**
	 * @param queryString
	 * @param isHql  是否是hql查询 ，true创建Query，否则创建SQLQuery
	 * @param objects	queryString中绑定的变量
	 * @return	查询总量
	 * @throws DAOException
	 * @方法说明 以“?”形式的绑定变量的sql
	 * @date 2011-11-30
	 * @author HeDroid
	 */
	protected int getCount( String queryString,boolean isHql, Object...objects) throws DAOException{
		return this.getCount(queryString, isHql, false,null, objects);
	}

	/**
	 * @param queryString
	 * @param isHql  是否是hql查询 ，true创建Query，否则创建SQLQuery
	 * @param isAlice 绑定变量的形式，true为别名绑定，false为“？”形式绑定。
	 * @param objects	queryString中绑定的变量
	 * @return	查询总量
	 * @throws DAOException
	 * @方法说明
	 * @date 2011-11-30
	 * @author HeDroid
	 */
	protected int getCount( String queryString,boolean isHql,boolean isAlice, Map<String,Object> param, Object...objects) throws DAOException{
		try{
			if(!StringUtils.checkString(queryString)){
				return 0;
			}else{
				//截去"ORDER BY"字符串
				//int index=queryString.trim().toLowerCase().indexOf("from");
				//是否包含别名的sql
				//int indexT=queryString.trim().toLowerCase().indexOf("{");
				//if(index==0 || indexT!=-1)
				//if(queryString.toLowerCase().indexOf("distinct")==-1&&queryString.toLowerCase().indexOf("group")==-1)
				try {
					String reSql = this.subStrQueryString(queryString);
					StringBuffer sqlCount = new StringBuffer();
					if(!checkQueryString(reSql))
						sqlCount.append("select count(*) ").append(reSql.substring(reSql.toLowerCase().indexOf("from ")));
					else
						sqlCount.append("select count(*) from (").append(reSql).append(")");
					
					//		StringBuffer sqlCount = new StringBuffer("select count(*) as totalcount from (");
					//		sqlCount.append(queryString).append(")");
					Query query=null;
					if(isHql){
						query=this.getSession().createQuery(sqlCount.toString());
					}else{
						query=this.getSession().createSQLQuery(sqlCount.toString());
					}
					if(isAlice){
						if(param!=null){
							for(String key:param.keySet()){
								Object value=param.get(key);
								if (value instanceof Collection) {
									query.setParameterList(key, (Collection) value);
								}
								else if (value instanceof Object[]) {
									query.setParameterList(key, (Object[]) value);
								}
								else {
									query.setParameter(key, value);
								}
							}
						}
					}else{
						for(int i = 0;i < objects.length; i++){
							query.setParameter(i, objects[i]);
						}
					}
					return Integer.parseInt(query.uniqueResult().toString());
				} catch (Exception e) {
					StringBuffer sqlCount = new StringBuffer();
					if(!checkQueryString(queryString))
						sqlCount.append("select count(*) ").append(queryString.substring(queryString.toLowerCase().indexOf("from ")));
					else
						sqlCount.append("select count(*) from (").append(queryString).append(")");
					
					//		StringBuffer sqlCount = new StringBuffer("select count(*) as totalcount from (");
					//		sqlCount.append(queryString).append(")");
					Query query=null;
					if(isHql){
						query=this.getSession().createQuery(sqlCount.toString());
					}else{
						query=this.getSession().createSQLQuery(sqlCount.toString());
					}
					if(isAlice){
						if(param!=null){
							for(String key:param.keySet()){
								Object value=param.get(key);
								if (value instanceof Collection) {
									query.setParameterList(key, (Collection) value);
								}
								else if (value instanceof Object[]) {
									query.setParameterList(key, (Object[]) value);
								}
								else {
									query.setParameter(key, value);
								}
							}
						}
					}else{
						for(int i = 0;i < objects.length; i++){
							query.setParameter(i, objects[i]);
						}
					}
					return Integer.parseInt(query.uniqueResult().toString());
				}
			}
//			return (Integer) query.list().iterator().next();
		}catch(Exception e){
			e.printStackTrace();
			log.error("Error occurred when getCount methods of AbstractBaseDAO.",e);
			throw new DAOException("查询总量失败失败", e);
		}
	}
	
	/**
	 * @param sql sql
	 * @param page 分页对象
	 * @param args 参数
	 * @return
	 * @throws DAOException
	 * @方法说明  返回分页对象，分页对象里面list里面放的是map，键是字段名称，值是对应字段值
	 * @date 2012-10-15
	 * @author 孙伟
	 */
	public Page findPageMapBySql(String sql,Page page,Object[] args) throws DAOException{
		try{
			int total = findCount(sql,args);
			List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
			if(total>0){
				page.setTotalrows(total);
				page.excecute();
				StringBuffer sqlPage = new StringBuffer();
				sqlPage.append("select * from (select page.*,rownum rn from (");
				sqlPage.append(sql);
				sqlPage.append(") page where rownum<=? ) where rn>=?");
				if(args.length==0){
					args = new Object[]{(page.getStart()+page.getRowsize()),page.getStart()+1};
					//argsType = new int[]{java.sql.Types.INTEGER,java.sql.Types.INTEGER};
				}else{
					Object[] objArray = new Object[args.length+2];
					//int[] objTypes = new int[argsType.length+2];
					for(int i=0;i<args.length;i++){
						objArray[i] = args[i];
						//objTypes[i] = argsType[i];
					}
					objArray[args.length] = page.getStart()+page.getRowsize();
					objArray[args.length+1] = page.getStart()+1;
					
					//objTypes[argsType.length] = java.sql.Types.INTEGER;
					//objTypes[argsType.length+1] = java.sql.Types.INTEGER;
					args = objArray;
					//argsType = objTypes;
				}
				//list = this.getJdbcTemplate().queryForList(sql, args, argsType);
				list = this.getJdbcTemplate().queryForList(sqlPage.toString(), args);
				page.setList(list);
			}
			return page;
		}catch(Exception e){
			log.error("Error occurred when findBySql methods of AbstractBaseDAO.",e);
			throw new DAOException("jdbc查询失败", e);
		}
	}
	
	/**
	 * @param sql语句
	 * @param page分页
	 * @param args参数数组
	 * @param argTypes参数类型数组，要和参数对应
	 * @param rowMapper自定义接口，实现RowMapper<T>，用于结果循环赋值
	 * @return
	 * @方法说明 jdbc分页查询
	 * @date 2011-12-21
	 * @author 孙伟
	 */
	public Page findBySql(String sql,Page page,Object[] args,RowMapper<T> rowMapper) throws DAOException{
		try{
			int total = findCount(sql,args);
			List<T> list = new ArrayList<T>();
			if(total>0){
				page.setTotalrows(total);
				page.excecute();
				StringBuffer sqlPage = new StringBuffer();
				sqlPage.append("select * from (select page.*,rownum rn from (");
				sqlPage.append(sql);
				sqlPage.append(") page where rownum<=? ) where rn>=?");
				if(args.length==0){
					args = new Object[]{(page.getStart()+page.getRowsize()),page.getStart()+1};
				}else{
					Object[] objArray = new Object[args.length+2];
					for(int i=0;i<args.length;i++){
						objArray[i] = args[i];
					}
					objArray[args.length] = page.getStart()+page.getRowsize();
					objArray[args.length+1] = page.getStart()+1;
					args = objArray;
				}
				list = this.getJdbcTemplate().query(sqlPage.toString(),
						args,
						rowMapper);
				page.setList(list);
			}
			return page;
		}catch(Exception e){
			log.error("Error occurred when findBySql methods of AbstractBaseDAO.",e);
			throw new DAOException("jdbc查询失败", e);
		}
	}
	
	/**
	 * @param sql语句
	 * @param page分页
	 * @param args参数数组
	 * @param argTypes参数类型数组，要和参数对应
	 * @param rowMapper自定义接口，实现RowMapper<T>，用于结果循环赋值
	 * @return
	 * @方法说明 jdbc分页查询
	 * @date 2011-12-21
	 * @author 孙伟
	 */
	public <X> Page findBySql(String sql,Page page,Object[] args,Class<X> clazz,RowMapper<X> rowMapper) throws DAOException{
		try{
			int total = findCount(sql,args);
			List<X> list = new ArrayList<X>();
			if(total>0){
				page.setTotalrows(total);
				page.excecute();
				StringBuffer sqlPage = new StringBuffer();
				sqlPage.append("select * from (select page.*,rownum rn from (");
				sqlPage.append(sql);
				sqlPage.append(") page where rownum<=? ) where rn>=?");
				if(args.length==0){
					args = new Object[]{(page.getStart()+page.getRowsize()),page.getStart()+1};
				}else{
					Object[] objArray = new Object[args.length+2];
					for(int i=0;i<args.length;i++){
						objArray[i] = args[i];
					}
					objArray[args.length] = page.getStart()+page.getRowsize();
					objArray[args.length+1] = page.getStart()+1;
					args = objArray;
				}
				list = this.getJdbcTemplate().query(sqlPage.toString(),
						args,
						rowMapper);
				page.setList(list);
			}
			return page;
		}catch(Exception e){
			log.error("Error occurred when findBySql methods of AbstractBaseDAO.",e);
			throw new DAOException("jdbc查询失败", e);
		}
	}
	
	/**
	 * @param mySql 业务sql
	 * @param userRoleCol 业务表角色实例字段名
	 * @param userRoleId  角色实例ID，通常是当前登录的角色实例ID
	 * @param args  绑定变量参数
	 * @return
	 * @方法说明 获取数据权限sql,接受角色实例Id,查询该角色实例的数据权限机构,然后查询该机构下所有子机构的所有角色实例
	 * @date 2012-5-5
	 * @author 孙伟
	 */
	public StringBuffer appendUserRoleData(StringBuffer mySql,String userRoleCol,Long userRoleId,List<Object> args){
		mySql.append(" and ");
		mySql.append(userRoleCol);
		mySql.append(" in (");
		mySql.append(" select e.base_user_role_id from base_user_role e,(");
		mySql.append(" select n.org_id from base_organization n start with n.org_id in (");
		mySql.append(" select t.org_id from base_user_role_data t where t.base_user_role_id=?");
		//mySql.append(userRoleId);
		mySql.append(" ) connect by prior n.org_id=n.parent_id) d where e.org_id=d.org_id )");
		args.add(userRoleId);
		return mySql;
	}
	
	/**
	 * @param mySql 业务sql
	 * @param userRoleCol 业务表的角色实例字段名称
	 * @param orgId  机构ID
	 * @param roleId 角色Id
	 * @param args 绑定变量参数
	 * @return
	 * @方法说明 根据机构ID和角色Id查询角色实例ID
	 * @date 2012-5-18
	 * @author 孙伟
	 */
	public StringBuffer appendUserRoleByOrgAndRoel(StringBuffer mySql,String userRoleCol,Long orgId,Long roleId,List<Object> args){
		mySql.append(userRoleCol);
		mySql.append(" in (");
		mySql.append("select userrole.base_user_role_id from base_user_role userrole where userrole.org_id=? and userrole.role_id=? and userrole.user_role_state=1");
		mySql.append(")");
		args.add(orgId);
		args.add(roleId);
		return mySql;
	}
	
	/**
	 * @param sql
	 * @param args参数
	 * @param argTypes参数类型
	 * @return 
	 * @throws DAOException
	 * @方法说明jdbc查询获取数据总量 
	 * @date 2011-12-21
	 * @author 孙伟
	 */
	public int findCount(String sql,Object[] args) throws DAOException{
		try{
			if(StringUtils.checkString(sql)){
				//截去"ORDER BY"字符串
				try {
					String reSql = this.subStrQueryString(sql);
					StringBuffer sbCount = new StringBuffer("select count(1) as count from(");
					sbCount.append(reSql);
					sbCount.append(")");
					return this.getJdbcTemplate().queryForInt(sbCount.toString(), args);
				} catch (Exception e) {
					StringBuffer sbCount = new StringBuffer("select count(1) as count from(");
					sbCount.append(sql);
					sbCount.append(")");
					return this.getJdbcTemplate().queryForInt(sbCount.toString(), args);
				}
			}else{
				return 0;
			}
		}catch(Exception e){
			log.error("Error occurred when findCount methods of AbstractBaseDAO.",e);
			throw new DAOException("jdbc查询总量失败失败", e);
		}
	}
	
	/**
	 * @param dataIds 数据主键和版本号，例如1234,1;4567,2
	 * @return
	 * @throws DAOException
	 * @方法说明 验证数据是否过期
	 * @date 2012-8-22
	 * @author 孙伟
	 */
	public boolean baseCheckDataValid(String dataIds) throws DAOException{
		try{
			if(dataIds==null||dataIds.trim().equals("")) return false;
			StringBuffer sql = new StringBuffer();
			sql.append("select count(1) as count from ");
			sql.append(HibernateConfigurationHelper.getTableName(modelClass));
			sql.append(" t where t.");
			sql.append(HibernateConfigurationHelper.getPkColumnName(modelClass));
			sql.append("||','||t.version ");
			sql.append(" in(");
			StringBuffer idSbf = new StringBuffer();
			///List<Object> args = new ArrayList<Object>();
			String[] idStr = dataIds.split(";");
			int length = idStr.length;
			for(int i=0;i<length;i++){
				idSbf.append("'").append(idStr[i]).append("'");
				//args.add(idStr[i]);
				if(i<length-1) idSbf.append(",");
			}
			sql.append(idSbf).append(")");
			int count = Integer.parseInt(this.getSession().createSQLQuery(sql.toString()).uniqueResult().toString());
			return count<length?false:true;
		}catch(Exception e){
			log.error("Error occurred when checkDataValid methods of AbstractBaseDAO.",e);
			throw new DAOException("验证数据是否过期失败", e);
		}
	}
	
	/**
	 * @param <X>
	 * @param dataIds 数据主键和版本号，例如1234,1;4567,2
	 * @param classX
	 * @return
	 * @throws DAOException
	 * @方法说明 验证数据是否过期,带泛型支持
	 * @date 2012-8-22
	 * @author 孙伟
	 */
	public <X> boolean baseCheckDataValid(String dataIds,Class<X> classX) throws DAOException{
		try{
			if(dataIds==null||dataIds.trim().equals("")) return false;
			StringBuffer sql = new StringBuffer();
			sql.append("select count(1) as count from ");
			sql.append(HibernateConfigurationHelper.getTableName(classX));
			sql.append(" t where t.");
			sql.append(HibernateConfigurationHelper.getPkColumnName(classX));
			sql.append("||','||t.version ");
			sql.append(" in(");
			StringBuffer idSbf = new StringBuffer();
			///List<Object> args = new ArrayList<Object>();
			String[] idStr = dataIds.split(";");
			int length = idStr.length;
			for(int i=0;i<length;i++){
				idSbf.append("'").append(idStr[i]).append("'");
				//args.add(idStr[i]);
				if(i<length-1) idSbf.append(",");
			}
			sql.append(idSbf).append(")");
			int count = Integer.parseInt(this.getSession().createSQLQuery(sql.toString()).uniqueResult().toString());
			return count<length?false:true;
		}catch(Exception e){
			log.error("Error occurred when checkDataValid methods of AbstractBaseDAO.",e);
			throw new DAOException("验证数据是否过期失败", e);
		}
	}
	
	/** 
	 * @param queryString
	 * @return
	 * @方法说明 检查查询的sql是否包含特殊字符
	 * @date 2012-2-28
	 * @author HeDroid
	 */
	private boolean checkQueryString(String queryString){
		if(queryString==null)
			return false;
		String[] array=queryString.trim().toLowerCase().split(" ");
		List<String> list= Arrays.asList(array);
		return list.contains("distinct")||list.contains("group")||list.contains("case")||list.contains("(case");
	}
	
	
	/**
	 * 
	 * @param ids id数组
	 * @param name 字段名
	 * @param groupNo 每组数量，不传默认为1000
	 * @return 返回自动截取后的sql
	 * @throws DAOException
	 */
	public StringBuffer appendIds(Long[] ids,String name,int groupNo)throws DAOException{
		int GROUPNO = groupNo==0?1000:groupNo;
		StringBuffer sql=new StringBuffer(" ");
		if (ids.length <= GROUPNO) {
			sql.append(name).append(" IN (");
			sql.append(net.uni.util.StringUtils.combineStringArray(ids, ","));
			sql.append(")");
		} else {
			int lastLength = GROUPNO;
			int count = 0;
			if (ids.length % GROUPNO == 0) {
				count = ids.length / GROUPNO;
			} else {
				count = ids.length / GROUPNO + 1;
				lastLength = ids.length % GROUPNO;
			}
			for (int i = 0; i < count; i++) {
				Long[] shortIds = new Long[i == count - 1 ? lastLength : GROUPNO];
				System.arraycopy(ids, i * GROUPNO, shortIds, 0,
						i == count - 1 ? lastLength : GROUPNO);
				if (i == 0) {
					sql.append(" (").append(name).append(" IN (");
				} else {
					sql.append(" OR ").append(name).append(" IN (");
				}
				sql.append(net.uni.util.StringUtils.combineStringArray(shortIds,
						","));
				sql.append(")");
			}
			sql.append(")");
		}
		return sql;
	}
	/**
	 * 
	 * @param ids id数组
	 * @param name 字段名
	 * @param groupNo 每组数量，不传默认为1000
	 * @return 返回自动截取后的sql
	 * @throws DAOException
	 */
	public StringBuffer appendIds(String[] ids,String name,int groupNo)throws DAOException{
		int GROUPNO = groupNo==0?1000:groupNo;
		StringBuffer sql=new StringBuffer(" ");
		if (ids.length <= GROUPNO) {
			sql.append(name).append(" IN (");
			sql.append(net.uni.util.StringUtils.combineStringArray(ids, ","));
			sql.append(")");
		} else {
			int lastLength = GROUPNO;
			int count = 0;
			if (ids.length % GROUPNO == 0) {
				count = ids.length / GROUPNO;
			} else {
				count = ids.length / GROUPNO + 1;
				lastLength = ids.length % GROUPNO;
			}
			for (int i = 0; i < count; i++) {
				Long[] shortIds = new Long[i == count - 1 ? lastLength : GROUPNO];
				System.arraycopy(ids, i * GROUPNO, shortIds, 0,
						i == count - 1 ? lastLength : GROUPNO);
				if (i == 0) {
					sql.append(" (").append(name).append(" IN (");
				} else {
					sql.append(" OR ").append(name).append(" IN (");
				}
				sql.append(net.uni.util.StringUtils.combineStringArray(shortIds,
						","));
				sql.append(")");
			}
			sql.append(")");
		}
		return sql;
	}
	public StringBuffer appendIds(String idsStr,String name,int groupNo)throws DAOException{
		String[] ids = new String[0];
		if(null!=idsStr){
			ids=idsStr.split(",");
		}
		return appendIds(ids, name, groupNo);
	}

	/**
	 * 根据sequence名称获取下一个值
	 * @return 如果参数为空则返回null
	 * @throws DAOException
	 */
	public Long getNextSequenceByName(String seqName) throws DAOException{
		try{
			if(null!=seqName&&!"".equals(seqName)){
				String sql="select "+seqName+".nextval FROM dual";
				Long nextValue = getJdbcTemplate().queryForLong(sql);
				return nextValue;
			}
			else{
				return null;
			}
		}catch (Exception e) {
			throw new DAOException("获取下一个序列值失败！",e);
		}
		
	}
	
	/**
	 * 
	 * @param sql
	 * @throws DAOException
	 * @方法说明 截去SQL中"ORDER BY"关键字
	 * @date 2014-5-13
	 * @author zhangrui
	 */
	public String subStrQueryString(String sql) throws DAOException{
		try {
			String reSql = "";
			if(StringUtils.checkString(sql)){
				//最后一次出现"ORDER BY"关键字的位置
				int lastOB = sql.toUpperCase().lastIndexOf("ORDER BY");
				if(lastOB > 0){
					//最后一个"ORDER BY"之后第一个")"的位置
					int firstBra = sql.indexOf(")", lastOB);
					if(firstBra > 0){
						reSql = sql.substring(0, lastOB) + sql.substring(firstBra, sql.length());
					}else{
						reSql = sql.substring(0, lastOB);
					}
				}
			}
			return reSql;
		} catch (Exception e) {
			throw new DAOException("去除SQL中ORDER BY失败", e);
		}
	}
	
	@Override
	public void flush() {
	}

	@Override
	public void clear() {
	}

	@Override
	public Integer getSystemMonth() throws DAOException {
		return null;
	}

	@Override
	public String batchOperSql(String joinId, Integer type) throws DAOException {
		return null;
	}
	
}


